/*
 * 中北大学软件学院版权所有2020-2025
 */
package nuc.lp.uolab.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import nuc.lp.uolab.user.vo.User;
import nuc.lp.uolab.util.DbUtil;
import nuc.lp.uolab.util.Md5Util;

/**
 * UserDao实现类
 * @author liupeng
 * @version 1.0
 */
public class UserDaoImpl implements UserDao {

	@Override
	public List<User> selectUser(String uname, String dname, String major, String classes, String grade) {
		Connection conn = DbUtil.getConnection();
		//非预编译对象
		Statement stmt = null;
		ResultSet rs = null;
		List<User> ulist = new ArrayList<User>();
		//因为我们不知道用户具体填了几个，所以这个sql语句是动态变化的
		String sql = "SELECT * FROM t_user WHERE 1=1 ";
		//可变字符串类
		StringBuilder stringBuilder = new StringBuilder(sql);
		if(uname != null) {
			stringBuilder.append(" AND username='" + uname + "'");
		}
		if(dname != null) {
			stringBuilder.append(" AND dname='" + dname + "'");
		}
		if(major != null) {
			stringBuilder.append(" AND major_name='" + major + "'");
		}
		if(classes != null) {
			stringBuilder.append(" AND cname='" + classes + "'");
		}
		if(grade != null) {
			stringBuilder.append(" AND grade='" + grade + "'");
		}
		sql = stringBuilder.toString();
		System.out.println(sql);
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			//把rs结果集里面的数据转移到集合userList中
			while(rs.next()){
				User user = new User();
				//rs.get里的username是表里的字段
				user.setUsername(rs.getString("username"));
				user.setDname(rs.getString("dname"));
				user.setMarjorName(rs.getString("major_name"));
				user.setCname(rs.getString("cname"));
				user.setGrade(rs.getString("grade"));
				user.setTelephone(rs.getString("telephone"));
				user.setQq(rs.getString("qq"));
				user.setDuty(rs.getString("duty"));
				user.setStatu(rs.getString("status"));
				ulist.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(conn, null, rs);
		}
		
		return ulist;
	}

	@Override
	public int deleteUser(String userid) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement pstmt = null;
		int count =0;
		
		String sql = "DELETE FROM t_user WHERE uid=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(userid));
			count = pstmt.executeUpdate();
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(conn, pstmt, null);
		}
		
		return count;
	}

	@Override
	public int updateUser(User user) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement pstmt = null;
		int count = 0;
		
		try {
			String sql = "UPDATE t_user SET telephone=?,qq=?,duty=?,remark=? WHERE uid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getTelephone());
			pstmt.setString(2, user.getQq());
			pstmt.setString(3, user.getDuty());
			pstmt.setString(4, user.getRemark());
			pstmt.setInt(5, user.getUid());
			//count表实执行的sql语句影响到的记录行数
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(conn, pstmt, null);
		}
		
		return count;
	}

	public boolean insertUser(User user) {
		boolean flag = false ;
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DbUtil.getConnection();
		
		String sql = "INSERT INTO t_user VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, Md5Util.getMD5(user.getPassword()));
			pstmt.setString(4, user.getRealname());
			pstmt.setString(5, user.getSex());
			pstmt.setString(6, user.getTelephone());
			pstmt.setString(7, user.getDname());
			pstmt.setString(8, user.getMarjorName());
			pstmt.setString(9, user.getCname());
			pstmt.setString(10, user.getGrade());
			pstmt.setString(11, user.getDuty());
			pstmt.setString(12, user.getQq());
			pstmt.setString(13, user.getStatu());
			pstmt.setString(14, user.getRemark());
			//这个方法执行的是insert,update,delete语句
			//如果执行成功返回非零int，失败返回零
			int count = pstmt.executeUpdate();
			if(count != 0) {
				//添加成功
				flag = true;
			}else {
				flag = false; 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(conn, pstmt, null);
		}
		
		return flag;
	}

	@Override
	public boolean login(String userName, String userPsw) {
		//数据库连接
		Connection conn = null;
		//结果集，查询得到的结果
		ResultSet rs = null;
		//
		PreparedStatement pstmt = null;
		//登录是否成功的标志
		boolean loginFlag = false;
		//打开数据库连接
		conn = DbUtil.getConnection();
		//准备SELECT语句;?叫做占位符
		String sql = "SELECT * FROM t_user WHERE username=? AND password=?";
		//准备sql语句
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//给?赋值
		try {
			pstmt.setString(1, userName);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pstmt.setString(2, userPsw);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//执行sql语句，rs就是查到的结果
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//rs里有数据的话，next（）返回true
		try {
			if(rs.next()) {
				loginFlag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(conn, pstmt, rs);
		}
		return loginFlag;
	}

}
